﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using System.Data.SqlClient;
using SystemDALInterface;
using SystemModels;

namespace SystemDAL
{
    /// <summary>
    /// 实现IUserManager接口，返回Users对象
    /// </summary>
    public class UserDBManager : IUserManager
    {
        DbContext db = new DbContext();
        #region 用户是否存在，返回用户对象
        /// <summary>
        /// 用户是否存在,返回用户对象
        /// </summary>
        /// <param name="userId">用户名</param>
        /// <param name="pwd">密码</param>
        /// <returns>返回用户对象</returns>
        public Users CheckUser(int userId, String pwd)
        {
            Users user = null;
            string sql = string.Format(@"select * from Users where userId={0} and userPwd={1}", userId, pwd);
            SqlDataReader dr = db.ExecReader(sql);
            if (dr.Read())
            {
                user = new Users();
                user.UserId = (int)dr["userId"];
                user.UserName = dr["userName"].ToString();
                user.UserPwd = dr["userPwd"].ToString();
            }   
            return user;
        }


        #endregion

        #region 查询所有的用户信息
        /// <summary>
        /// 张琪 
        /// 查询所有的用户信息
        /// </summary>
        /// <returns>用户集合</returns>
        public List<Users> GetAllUsers()
        {
            List<Users> users = new List<Users>();
            string sql = string.Format(@"select * from Users");
            SqlDataReader dr = db.ExecReader(sql);
            while (dr.Read())
            {
                Users user = new Users();
                user.UserId = (int)dr["userId"];
                user.UserName = dr["userName"].ToString();
                user.UserPwd = dr["userPwd"].ToString();
                user.UserStatus = (int)dr["userStatus"];
                users.Add(user);
            }
            return users;
        }
        #endregion

        /*
        #region  张琪 删除用户
        /// <summary>
        /// 张琪 删除用户
        /// </summary>
        /// <param name="userId">userId 用户id</param>
        /// <returns>int 受影响的行数</returns>
        public int DeleteUser(int userId)
        {
            //受影响的行数
            int count = 0;
            //创建连接对象
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                //打开数据库连接
                conn.Open();
                //sql 语句
                StringBuilder sb = new StringBuilder();
                sb.AppendLine("delete UserRole ");
                sb.AppendFormat("where userId=@userId;", userId);

                sb.AppendLine("delete Results ");
                sb.AppendFormat("where userId=@userId;", userId);

                sb.AppendLine("delete CheckQuestion ");
                sb.AppendFormat("where userId=@userId;", userId);

                sb.AppendLine("delete Users ");
                sb.AppendFormat("where userId=@userId;", userId);

                SqlCommand cmd = new SqlCommand(sb.ToString(), conn);
                cmd.Parameters.Add("@userId", SqlDbType.NVarChar, 30).Value = userId;
                count = cmd.ExecuteNonQuery();
            };
            return count;
        }
        #endregion

        #region 阚秋燕 添加用户
        /// <summary>
        /// 添加用户
        /// </summary>
        /// <param name="userName">userName 用户名</param>
        /// <param name="pwd">pwd 密码</param>
        /// <returns>int 受影响的行数</returns>
        public int AddUser(string userName, string pwd)
        {
            int count = 0;
            using (SqlConnection con = new SqlConnection(connStr))
            {
                //打开数据库连接对象
                con.Open();
                string sql = "insert into Users values(@userName,@userPwd,0)";
                //执行语句
                SqlCommand cmd = new SqlCommand(sql, con);
                //设置参数
                cmd.Parameters.Add("@userName", SqlDbType.NVarChar, 30).Value = userName;
                cmd.Parameters.Add("@userPwd", SqlDbType.NVarChar, 30).Value = pwd;
                count = (int)cmd.ExecuteNonQuery();
            }
            //返回
            return count;
        }
        #endregion

        #region 阚秋燕 根据用户名查询用户信息
        /// <summary>
        /// 根据用户名查询用户信息
        /// </summary>
        /// <param name="userName">userName 用户名</param>
        /// <returns>Users 用户</returns>
        public Users QueryUserByName(string userName)
        {
            //根据姓名查找该用户名的ID
            //创建User对象
            Users user = new Users();
            using (SqlConnection con = new SqlConnection(connStr))
            {
                con.Open();
                StringBuilder sb = new StringBuilder();
                sb.AppendLine("select  *");
                sb.AppendLine("from Users ");
                sb.AppendFormat("where userName=@userName", userName);
                SqlCommand cmd = new SqlCommand(sb.ToString(), con);
                cmd.Parameters.Add("@userName", SqlDbType.NVarChar, 50).Value = userName;
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    //读取数据
                    if (reader.Read())
                    {
                        user.UserId = (int)reader["userId"];
                        user.UserName = reader["userName"].ToString();
                        user.UserPwd = reader["userPwd"].ToString();
                        user.UserStatus = (int)reader["userStatus"];
                    }
                }
                return user;
            }
        }
        #endregion

        #region 阚秋燕 (修改用户名)
        /// <summary>
        /// 修改用户姓名
        /// </summary>
        /// <param name="userId">userName</param>
        /// <param name="userName">返回Int</param>
        /// <returns></returns>
        public int ChangeUserName(int userId, string userName)
        {
            using (SqlConnection con = new SqlConnection(connStr))
            {
                con.Open();
                string sql = "update Users set UserName=@UserName where UserId=@UserId";
                SqlCommand cmd = new SqlCommand(sql, con);
                cmd.Parameters.Add("UserName", SqlDbType.NVarChar, 50).Value = userName;
                cmd.Parameters.Add("UserId", SqlDbType.Int).Value = userId;
                int count = cmd.ExecuteNonQuery();
                return count;
            }
        }
        #endregion

        #region 张琪 修改用户密码
        /// <summary>
        /// 修改用户密码
        /// </summary>
        /// <param name="userId">用户ID</param>
        /// <param name="pwd">用户新密码</param>
        /// <returns>受影响的行数</returns>
        public int ChangeUserPwd(int userId, string pwd)
        {
            //定义受影响的行数
            int rows = 0;
            //sql语句
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("update Users");
            sb.AppendLine("set");
            sb.AppendLine(" userPwd = @userPwd");
            sb.AppendLine("where ");
            sb.AppendLine("userId = @userId");
            //创建连接对象
            using (SqlConnection con = new SqlConnection(connStr))
            {
                //打开数据库
                con.Open();
                SqlCommand cmd = new SqlCommand(sb.ToString(), con);
                cmd.Parameters.Add(new SqlParameter("@userPwd", SqlDbType.NVarChar, 30)).Value = pwd;
                cmd.Parameters.Add(new SqlParameter("@userId", SqlDbType.Int)).Value = userId;
                rows = cmd.ExecuteNonQuery();
            }
            return rows;
        }
        #endregion
        */

        #region 修改用户状态
        /// <summary>
        /// 修改用户状态
        /// </summary>
        /// <param name="user">用户</param>
        /// <returns>bool</returns>
        public bool ChangeUser(Users user)
        {
            bool con = false;
            //sql语句
            string sql = string.Format("update Users set userStatus = {0} where userId = {1}", user.UserStatus, user.UserId);
            con = db.ExecNonQuery(sql);
            return con;
        }
        #endregion
         
    }
}
